Generated code - Using dynamic lists, Adapter
Preface
LLBLGen Pro offers you to create lists in code, without the necessity of the designer. This sometimes can become handy if you just want to
pull a small list of data from the database without having to re-generate the code again.
This section is largely meant for people who have to work with code bases
which contain dynamic list creating code: with Linq and QuerySpec, it's
easier to create dynamic lists and projection queries than using the more
verbose ResultsetFields method described below. The example contains
alternative queries using QuerySpec and Linq to show how to fetch the same
resultset using these methodologies. It's recommended you use QuerySpec
instead of the ResultsetFields methods in new code.
In the example
below, the 'low-level API' variant is the original dynamic list code, using
ResultsetFields. This methodology is still fully supported, just keep in
mind that QuerySpec and Linq offer a less verbose alternative.
The following paragraph discusses briefly how to create a
dynamic list in code. Dynamic lists are using the same building blocks as Typed View and Typed List classes use and can be used with
normal filters and other constructs like group by.
Creating dynamic lists
Typed lists are great, however sometimes you need a small list of data, build from one or more entities and use it in a read-only way, and you don't really
need the typed functionality coming with a typed list. After all, a typed list requires you to go into the designer, create the list and re-generate the code.
Dynamic lists are based on entity fields, using the similar code as TypedList classes use internally. These lists are loaded into DataTable objects.
The following example shows you how to create such a dynamic list. The example uses
aggregates and a GroupByCollection to read a custom resultset into a DataTable,
fully build with entity fields. The example joins the same entity (
EmployeeEntity)
twice, hence it uses aliases to make sure the right entity is referenced by
the various elements.
- Low-level API, C#
- Low-level API, VB.NET
- QuerySpec, C#
- QuerySpec, VB.NET
- Linq, C#
// Low-level API, C#
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
DataTable dynamicList = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);
}
' Low-level API, VB.NET
Dim fields As New ResultsetFields(3)
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count)
Dim bucket As IRelationPredicateBucket = New RelationPredicateBucket()
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None)
Dim groupByClause As IGroupByCollection = New GroupByCollection()
groupByClause.Add(fields(0))
groupByClause.Add(fields(1))
Dim dynamicList As New DataTable()
Using adapter As New DataAccessAdapter()
adapter.FetchTypedList(fields, dynamicList, bucket, 0, Nothing, True, groupByClause)
End Using
// QuerySpec, C#
var qf = new QueryFactory();
var q = qf.Create()
.Select(() => new
{
FirstName = EmployeeFields.FirstName.Source("Manager").ToValue<string>(),
LastName = EmployeeFields.LastName.Source("Manager").ToValue<string>(),
NumberOfEmployees = Functions.CountRow().As("NumberOfEmployees").ToValue<int>()
})
.From(qf.Employee.InnerJoin(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager"))
.GroupBy(EmployeeFields.FirstName.Source("Manager"), EmployeeFields.LastName.Source("Manager"));
using(var adapter = new DataAccessAdapter())
{
var results = adapter.FetchQuery(q);
// process 'results' here, as it's a List<anonymousType>
}
' QuerySpec, VB.NET
Dim qf As New QueryFactory()
Dim q = qf.Create() _
.Select(Function() New With _
{
.FirstName = EmployeeFields.FirstName.Source("Manager").ToValue(Of String)(), _
.LastName = EmployeeFields.LastName.Source("Manager").ToValue(Of String)(), _
.NumberOfEmployees = Functions.CountRow().As("NumberOfEmployees").ToValue(Of Integer)() _
}) _
.From(qf.Employee.InnerJoin(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager")) _
.GroupBy(EmployeeFields.FirstName.Source("Manager"), EmployeeFields.LastName.Source("Manager"))
Using adapter As New DataAccessAdapter()
Dim results = adapter.FetchQuery(q)
' process 'results' here, as it's a List<anonymousType>
End Using
// Linq, C#
using(var adapter = new DataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
var q = from e in metaData.Employee
join m in metaData.Employee on e.ReportsTo equals m.EmployeeId
group m by new { m.FirstName, m.LastName } into g
select new
{
FirstName = g.Key.FirstName,
LastName = g.Key.LastName,
NumberOfEmployees = g.Count()
};
var results = q.ToList();
}
This list retrieves all managers and the number of employees they manage. Let's walk through the example to make it more understandable.
It first creates a list of fields which will form the list.
ResultsetFields
is a class defined in the
HelperClasses namespace in your generated code
(database generic project) and which is a class derived from
EntityFields2, the container for
EntityField2 objects which is also located in every entity: the Fields
property.
The three lines following
the declaration of the fields parameter define the three fields in detail. First, it specifies an entity field, to signal which field we want
on that position of the resultset fields, then the index of the field in the
ResultsetFields object, then the alias for the field in the resultset and
optionally (but we join Employee twice so we have to alias) the alias for the entity this field belongs to. The third field is actually the same as the second,
Employee.LastName, however has an aggregate function applied to it. LastName is not a numeric field, but the type of the field is not important when an aggregate
function is applied, as the field defines a column in the dynamic list and is used as a parameter for the aggregate function; the aggregate function itself, or
better the value it produces, is the actual value of the column and the type is determined at runtime. As a DataColumn object can contain any value, this works
as planned.
As we have to join Employee twice, we have to define a relation collection and add the relation required for the join. The entities in the relation are
properly aliased as "Employee" and "Manager" so the generated code knows from which table the fields should be retrieved. As we're going to group by, we define
the group by collection and add the fields which participate in the group by in the order in which we want to group. We don't add the third field, as it is an
aggregated field which is using the grouped data.
After that, the objects are setup to retrieve the data. We use the adapter's FetchTypedList method, as that
method is capable of fetching a set of data in a DataTable object. We could have specified a filter as well, additional relations for the filter,
and even paging parameters.
This way of creating lists of data is very flexible and can be easily extended with expressions for complex resultsets, for example for usage in reports.